package com.zzw.utils;

import jxl.*;
import jxl.format.UnderlineStyle;
import jxl.write.*;
import jxl.write.Number;

import java.io.*;
import java.lang.reflect.Field;
import java.util.*;

public class UtilExcel {

    public static <T> List<T> readExcel(String filePath, Class<T> beanClass) throws Exception {
        try {
            InputStream is = new FileInputStream(filePath);
            return readExcel(is, beanClass);
        } catch (Exception e) {
            throw new Exception("分析Excel文件失败");
        }
    }

    public static <T> List<T> readExcel(byte[] excelData, Class<T> beanClass) throws Exception {
        try {
            InputStream is = new ByteArrayInputStream(excelData);
            return readExcel(is, beanClass);
        } catch (Exception e) {
            throw new Exception("分析Excel文件失败");
        }
    }

    private static <T> List<T> readExcel(InputStream is, Class<T> beanClass) throws Exception {

        Workbook rwb = Workbook.getWorkbook(is);
        Sheet rst = rwb.getSheet(0);
        /*获取Sheet表中所包含的行*/
        int rsRows = rst.getRows();
        List<T> beans = new ArrayList<>();
        Field[] fields = beanClass.getDeclaredFields();
        //获取指定单元格的对象引用
        for (int i = 1; i < rsRows; i++) {
            T bean = beanClass.newInstance();
            for (int j = 0; j < fields.length; j++) {
                Cell cell = rst.getCell(j, i);
                Field field = fields[j];
                field.setAccessible(true);
                if (field.getType() == String.class) {
                    fields[j].set(bean, cell.getContents());
                } else if (field.getType() == int.class || field.getType() == Integer.class) {
                    if (cell != null && hasText(cell.getContents())) {
                        NumberCell nc = (NumberCell) cell;
                        fields[j].set(bean, (int) nc.getValue());
                    } else {
                        fields[j].set(bean, null);
                    }
                } else if (field.getType() == double.class || field.getType() == Double.class) {
                    if (cell != null && hasText(cell.getContents())) {
                        NumberCell nc = (NumberCell) cell;
                        fields[j].set(bean, nc.getValue());
                    } else {
                        fields[j].set(bean, null);
                    }
                } else if (field.getType() == Date.class) {
                    if (cell != null && hasText(cell.getContents())) {
                        DateCell dateCell = (DateCell) cell;
                        fields[j].set(bean, dateCell.getDate());
                    }else{
                        fields[j].set(bean, null);
                    }
                } else {
                    fields[j].set(bean, cell.getContents());
                }
            }
            beans.add(bean);
        }
        rwb.close();
        is.close();
        return beans;
    }

    private static boolean hasText(String s) {
        return s != null && s.trim().length() > 0;
    }

    /**
     * 输出Excel
     */

    @SuppressWarnings("deprecation")
	public static <T> void writeExcel(String filePath, List<T> dataList, Map<String, ExcelCellInfo> sheetHead) throws Exception {
        WritableWorkbook wwb = Workbook.createWorkbook(new FileOutputStream(filePath));
        //创建Excel工作表 指定名称和位置
        WritableSheet sheet = wwb.createSheet("Sheet 1", 0);

        //**************工作表中添加数据*****************//
        /*写入表头*/
        int col = 0;
        int row = 0;
        for (Map.Entry<String, ExcelCellInfo> entry : sheetHead.entrySet()) {
            //添加带有字体颜色的Formatting对象
            sheet.setColumnView(col, entry.getValue().getCellWidth());
            WritableFont wfc = new WritableFont(WritableFont.COURIER, 11, WritableFont.BOLD, false,
                    UnderlineStyle.NO_UNDERLINE, Colour.BLUE_GREY);
            WritableCellFormat wcfFC = new WritableCellFormat(wfc);
            jxl.write.Label labelCF = new jxl.write.Label(col, 0, entry.getValue().getHeadname(), wcfFC);
            sheet.addCell(labelCF);
            col++;
        }
        
        /*写入数据*/
        row = 1;
        for (T data : dataList) {
            col = 0;
            for (String propertyName : sheetHead.keySet()) {
                if (BasePropertyUtils.getPropertyType(data, propertyName) == String.class) {
                    String content = BasePropertyUtils.getPropertyValue(data, propertyName) == null ? null : BasePropertyUtils.getPropertyValue(data, propertyName).toString();
                    Label label = new Label(col, row, content);
                    sheet.addCell(label);
                } else if (BasePropertyUtils.getPropertyType(data, propertyName) == Date.class) {
                    if (BasePropertyUtils.getPropertyValue(data, propertyName) != null) {
                        Date content = (Date) BasePropertyUtils.getPropertyValue(data, propertyName);
                        DateFormat df = new DateFormat("yyyy-MM-dd");
                        WritableCellFormat wcfDF = new WritableCellFormat(df);
                        jxl.write.DateTime labelDTF = new DateTime(col, row, content, wcfDF);
                        sheet.addCell(labelDTF);
                    }
                } else if (BasePropertyUtils.getPropertyType(data, propertyName) == int.class || BasePropertyUtils.getPropertyType(data, propertyName) == Integer.class ||
                        BasePropertyUtils.getPropertyType(data, propertyName) == double.class || BasePropertyUtils.getPropertyType(data, propertyName) == Double.class) {
                    if (BasePropertyUtils.getPropertyValue(data, propertyName) != null) {
                        Number labelN = new Number(col, row, Double.valueOf(BasePropertyUtils.getPropertyValue(data, propertyName).toString()));
                        sheet.addCell(labelN);
                    }
                } else {
                    String content = BasePropertyUtils.getPropertyValue(data, propertyName) == null ? null : BasePropertyUtils.getPropertyValue(data, propertyName).toString();
                    Label label = new Label(col, row, content);
                    sheet.addCell(label);
                }
                col++;
            }
            row++;
        }
        //7.写入工作表
        wwb.write();
        wwb.close();
    }
    
    

    /** 将file1拷贝�?,进行修改并创建输出对象file2

     * 单元格原有的格式化修饰不能去掉，但仍可将新的单元格修饰加上去�?

     * 以使单元格的内容以不同的形式表现

     */

   /*public static void modifyExcel(File file1,File file2){
        try{
            Workbook rwb = Workbook.getWorkbook(file1);
            WritableWorkbook wwb = Workbook.createWorkbook(file2,rwb);//copy
            WritableFont wfc = new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false,
                    UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLUE);
            WritableCellFormat wcfFC = new WritableCellFormat(wfc);
            WritableSheet ws = wwb.getSheet(0);
            WritableCell wc = ws.getWritableCell(0,0);
            //判断单元格的类型,做出相应的转换
            if(wc.getType() == CellType.LABEL){
                Label labelCF =new Label(0,0,"人物（新,wcfFC);
                ws.addCell(labelCF);
                //Label label = (Label)wc;
                //label.setString("被修改");
            }
            wwb.write();
            wwb.close();
            rwb.close();
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }
*/
    //测试
 /*public static void main(String args[])

    {
        try

        {
            //读EXCEL

            ExcelHandle.readExcel("F:/红楼人物.xls");

            //输出EXCEL

            File filewrite=new File("F:/红楼人物2.xls");

            filewrite.createNewFile();

            OutputStream os=new FileOutputStream(filewrite);

            ExcelHandle.writeExcel(os);

            //修改EXCEL

            ExcelHandle.modifyExcel(new File("F:/红楼人物.xls"), new File("F:/红楼人物3.xls"));

        }catch(Exception e){

            e.printStackTrace();
        }

    }*/
}
